problem with transactions in VB.NET using npgsql - Mailing list pgsql-general

Maybe someone here can figure it out.  Everything updates fine with
this code, except where there's an exception, it's not rolling back
by the transaction.  What I'm trying to do:

Begin a transaction
Do the update, insert, delete checks on each of the data tables,
using a different npgsqlcommandbuilder for each of the tables.
Commit
if any failure happens, roll back all the changes to the transaction beginning.

I assign the transaction object to each of the commands, but it seems
that some tables will get updated, even when I call rollback.  Is
something I'm calling secretly calling "commit" somewhere?

My code follows.  Thanks for checking it over.  Sorry about the
length, but I wanted you to see that I'm updating multiple tables
with multiple dataadapters.

-Owen

Option Explicit On
Imports System.Windows.Forms
Imports npgsql
Imports System.Xml.Serialization
Imports System.IO
Imports System.Collections.Generic
Imports System.Configuration
' Note: some controls, in the forms designer, cover other controls,
i.e. CommUsageCB covers styleCB
Public Class ParcelDisplayFrm

     Public Sub WriteAllData()
         Dim trans As NpgsqlTransaction = Nothing
         Dim cmd As NpgsqlCommandBuilder
         Dim i As Integer
         Dim success As Boolean

         Try
             If Not statusReadOnly Then
                 i = vbCancel
                 success = priceIt(Me, full_DataSet, True, True, pc)
                 dt = full_DataSet.Tables(currentSchema & ".parcel")

                 dt.Rows(0).EndEdit()
                 dt = full_DataSet.Tables(currentSchema & ".accounts")
                 dt.Rows(0).EndEdit()
                 dt = full_DataSet.Tables(currentSchema & ".bldg")
                 For i = 0 To dt.Rows.Count - 1
                     dt.Rows(i).EndEdit()
                 Next i
                 dt = full_DataSet.Tables(currentSchema & ".commcost")
                 For i = 0 To dt.Rows.Count - 1
                     dt.Rows(i).EndEdit()
                 Next i
                 dt = full_DataSet.Tables(currentSchema & ".outbuildings")
                 For i = 0 To dt.Rows.Count - 1
                     If dt.Rows(i).RowState = DataRowState.Added Then
                         dt.Rows(i).Item("maplot") = Form1.currentMapLot
                     End If
                     Debug.Print(dt.Rows.Count)
                     dt.Rows(i).EndEdit()
                 Next i
                 If Not dirtySketch And Not full_DataSet.HasChanges Then
                     Exit Sub    ' Nothing to change
                 End If

                 Dim dg As New SaveChangesDlog
                 If dg.ShowDialog = Windows.Forms.DialogResult.Cancel
Then Exit Sub ' don't save
                 writeFinalize()
                 dt = full_DataSet.Tables(currentSchema & ".parcel")
                 m_SqlConnection.Open()
' create a transaction for the rest of all the changes

                 trans = m_SqlConnection.BeginTransaction

                 cmd = New NpgsqlCommandBuilder(parcel_DataAdapter)


                 Dim parcelchanges As DataTable =
dt.GetChanges(DataRowState.Modified)

                 If parcelchanges IsNot Nothing Then
                     parcel_DataAdapter.UpdateCommand =
cmd.GetUpdateCommand(dt.Rows(0))
                     parcel_DataAdapter.UpdateCommand.Transaction = trans

                     parcel_DataAdapter.Update(parcelchanges)
                 End If
                 parcelchanges = dt.GetChanges(DataRowState.Deleted)
                 If parcelchanges IsNot Nothing Then
                     parcel_DataAdapter.DeleteCommand =
cmd.GetDeleteCommand(dt.Rows(0))
                     parcel_DataAdapter.DeleteCommand.Transaction = trans

                     parcel_DataAdapter.Update(parcelchanges)
                 End If
                 parcelchanges = dt.GetChanges(DataRowState.Added)
                 If parcelchanges IsNot Nothing Then
                     parcel_DataAdapter.InsertCommand =
cmd.GetInsertCommand(dt.Rows(0))

                     parcel_DataAdapter.InsertCommand.Transaction = trans

                     parcel_DataAdapter.Update(parcelchanges)
                 End If

                 ' accounts table
                 cmd = New NpgsqlCommandBuilder(accts_DataAdapter)
                 dt = full_DataSet.Tables(currentSchema & ".accounts")
                 Dim acctchanges As DataTable =
dt.GetChanges(DataRowState.Modified)

                 If acctchanges IsNot Nothing Then
                     accts_DataAdapter.UpdateCommand =
cmd.GetUpdateCommand(dt.Rows(0))
                     accts_DataAdapter.UpdateCommand.Transaction = trans

                     accts_DataAdapter.Update(acctchanges)
                 End If
                 acctchanges = dt.GetChanges(DataRowState.Deleted)
                 If acctchanges IsNot Nothing Then
                     accts_DataAdapter.DeleteCommand =
cmd.GetDeleteCommand(dt.Rows(0))
                     accts_DataAdapter.DeleteCommand.Transaction = trans

                     accts_DataAdapter.Update(acctchanges)
                 End If
                 acctchanges = dt.GetChanges(DataRowState.Added)
                 If acctchanges IsNot Nothing Then
                     accts_DataAdapter.InsertCommand =
cmd.GetInsertCommand(dt.Rows(0))

                     accts_DataAdapter.InsertCommand.Transaction = trans

                     accts_DataAdapter.Update(acctchanges)
                 End If

                 ' do for every building
                 dt = full_DataSet.Tables(currentSchema & ".bldg")
                 If dt.Rows.Count > 0 Then
                     If dirtySketch Then
                         For i = currentBuilding To howManyBuildings - 1

returnSketchToDatabase(dt.Rows(0).Item("maplot"), i, trans, Me)
                         Next i
                     End If
                     cmd = New NpgsqlCommandBuilder(bldg_DataAdapter)

                     ' add modified dates
                     addModDates(dt, "modified")


                     'Debug.Print(ZoningCode.DataBindings.BindableComponent)
                     Dim bldgchanges As DataTable =
dt.GetChanges(DataRowState.Deleted)
                     If bldgchanges IsNot Nothing Then
                         bldg_DataAdapter.DeleteCommand =
cmd.GetDeleteCommand(dt.Rows(0))
                         bldg_DataAdapter.DeleteCommand.Transaction = trans

                         bldg_DataAdapter.Update(bldgchanges)
                     End If
                     bldgchanges = dt.GetChanges(DataRowState.Modified)
                     If bldgchanges IsNot Nothing Then
                         Dim j As Integer = 0
                         While dt.Rows(j).RowState = DataRowState.Deleted
                             j = j + 1
                         End While
                         bldg_DataAdapter.UpdateCommand =
cmd.GetUpdateCommand(dt.Rows(j))
                         bldg_DataAdapter.UpdateCommand.Transaction = trans

                         bldg_DataAdapter.Update(bldgchanges)
                     End If
                     bldgchanges = dt.GetChanges(DataRowState.Added)
                     If bldgchanges IsNot Nothing Then
                         bldg_DataAdapter.InsertCommand =
cmd.GetInsertCommand(dt.Rows(0))
                         bldg_DataAdapter.InsertCommand.Transaction = trans

                         bldg_DataAdapter.Update(bldgchanges)
                     End If
                 End If

                 dt = full_DataSet.Tables(currentSchema & ".commcost")
                 If dt.Rows.Count > 0 Then
                     cmd = New NpgsqlCommandBuilder(commbldg_DataAdapter)

                     'Debug.Print(ZoningCode.DataBindings.BindableComponent)
                     Dim commBldgChanges As DataTable
                     commBldgChanges = dt.GetChanges(DataRowState.Deleted)
                     If commBldgChanges IsNot Nothing Then
                         commbldg_DataAdapter.DeleteCommand =
cmd.GetDeleteCommand(dt.Rows(0))
                         commbldg_DataAdapter.DeleteCommand.Transaction = trans

                         commbldg_DataAdapter.Update(commBldgChanges)
                     End If
                     commBldgChanges = dt.GetChanges(DataRowState.Modified)
                     If commBldgChanges IsNot Nothing Then
                         Dim j As Integer = 0
                         While dt.Rows(j).RowState = DataRowState.Deleted
                             j = j + 1
                         End While
                         commbldg_DataAdapter.UpdateCommand =
cmd.GetUpdateCommand(dt.Rows(j))
                         commbldg_DataAdapter.UpdateCommand.Transaction = trans

                         commbldg_DataAdapter.Update(commBldgChanges)
                     End If
                     commBldgChanges = dt.GetChanges(DataRowState.Added)
                     If commBldgChanges IsNot Nothing Then
                         Dim j As Integer = 0
                         While dt.Rows(j).RowState = DataRowState.Deleted
                             j = j + 1
                         End While
                         commbldg_DataAdapter.InsertCommand =
cmd.GetInsertCommand(dt.Rows(j))
                         commbldg_DataAdapter.InsertCommand.Transaction = trans

                         commbldg_DataAdapter.Update(commBldgChanges)
                     End If
                 End If

                 dt = full_DataSet.Tables(currentSchema & ".outbuildings")
                 If dt.Rows.Count > 0 Then
                     cmd = New NpgsqlCommandBuilder(outbldg_DataAdapter)

                     For i = 0 To dt.Rows.Count - 1
                         If dt.Rows(i).RowState = DataRowState.Added Then
                             dt.Rows(i).Item("MapLot") =
full_DataSet.Tables(currentSchema & ".parcel").Rows(0).Item("MapLot")
                         End If
                     Next i
                     'Debug.Print(ZoningCode.DataBindings.BindableComponent)
                     Dim outchanges As DataTable
                     outchanges = dt.GetChanges(DataRowState.Deleted)
                     If outchanges IsNot Nothing Then
                         outbldg_DataAdapter.DeleteCommand =
cmd.GetDeleteCommand(dt.Rows(0))
                         outbldg_DataAdapter.DeleteCommand.Transaction = trans

                         outbldg_DataAdapter.Update(outchanges)
                     End If
                     outchanges = dt.GetChanges(DataRowState.Modified)
                     If outchanges IsNot Nothing Then
                         Dim j As Integer = 0
                         While dt.Rows(j).RowState = DataRowState.Deleted
                             j = j + 1
                         End While
                         outbldg_DataAdapter.UpdateCommand =
cmd.GetUpdateCommand(dt.Rows(j))
                         outbldg_DataAdapter.UpdateCommand.Transaction = trans

                         outbldg_DataAdapter.Update(outchanges)

                     End If
                     outchanges = dt.GetChanges(DataRowState.Added)
                     If outchanges IsNot Nothing Then
                         Dim j As Integer = 0
                         While dt.Rows(j).RowState = DataRowState.Deleted
                             j = j + 1
                         End While
                         outbldg_DataAdapter.InsertCommand =
cmd.GetInsertCommand(dt.Rows(j))
                         outbldg_DataAdapter.InsertCommand.Transaction = trans

                         outbldg_DataAdapter.Update(outchanges)
                     End If
                 End If
                 ' write changes to sales tables
                 dt = full_DataSet.Tables(currentSchema & ".sales")
                 If dt.Rows.Count > 0 Then
                     cmd = New NpgsqlCommandBuilder(sales_DataAdapter)

                     'Debug.Print(ZoningCode.DataBindings.BindableComponent)
                     Dim salesChanges As DataTable
                     salesChanges = dt.GetChanges(DataRowState.Deleted)
                     If salesChanges IsNot Nothing Then
                         sales_DataAdapter.DeleteCommand =
cmd.GetDeleteCommand(dt.Rows(0))
                         sales_DataAdapter.DeleteCommand.Transaction = trans

                         sales_DataAdapter.Update(salesChanges)
                     End If
                     salesChanges = dt.GetChanges(DataRowState.Modified)
                     If salesChanges IsNot Nothing Then
                         Dim j As Integer = 0
                         While dt.Rows(j).RowState = DataRowState.Deleted
                             j = j + 1
                         End While
                         sales_DataAdapter.UpdateCommand =
cmd.GetUpdateCommand(dt.Rows(j))
                         sales_DataAdapter.UpdateCommand.Transaction = trans

                         sales_DataAdapter.Update(salesChanges)
                     End If
                     salesChanges = dt.GetChanges(DataRowState.Added)
                     If salesChanges IsNot Nothing Then
                         Dim j As Integer = 0
                         While dt.Rows(j).RowState = DataRowState.Deleted
                             j = j + 1
                         End While
                         sales_DataAdapter.InsertCommand =
cmd.GetInsertCommand(dt.Rows(j))
                         sales_DataAdapter.InsertCommand.Transaction = trans

                         sales_DataAdapter.Update(salesChanges)
                     End If
                 End If
                 ' write changes to sales overflow table
                 dt = full_DataSet.Tables(currentSchema & ".salesovflowtype")
                 If dt.Rows.Count > 0 Then
                     cmd = New NpgsqlCommandBuilder(salesOF_DataAdapter)

                     'Debug.Print(ZoningCode.DataBindings.BindableComponent)
                     Dim salesOFChanges As DataTable
                     salesOFChanges = dt.GetChanges(DataRowState.Deleted)
                     If salesOFChanges IsNot Nothing Then
                         salesOF_DataAdapter.DeleteCommand =
cmd.GetDeleteCommand(dt.Rows(0))
                         salesOF_DataAdapter.DeleteCommand.Transaction = trans

                         salesOF_DataAdapter.Update(salesOFChanges)
                     End If
                     salesOFChanges = dt.GetChanges(DataRowState.Modified)
                     If salesOFChanges IsNot Nothing Then
                         Dim j As Integer = 0
                         While dt.Rows(j).RowState = DataRowState.Deleted
                             j = j + 1
                         End While
                         salesOF_DataAdapter.UpdateCommand =
cmd.GetUpdateCommand(dt.Rows(j))
                         salesOF_DataAdapter.UpdateCommand.Transaction = trans

                         salesOF_DataAdapter.Update(salesOFChanges)
                     End If
                     salesOFChanges = dt.GetChanges(DataRowState.Added)
                     If salesOFChanges IsNot Nothing Then
                         Dim j As Integer = 0
                         While dt.Rows(j).RowState = DataRowState.Deleted
                             j = j + 1
                         End While
                         salesOF_DataAdapter.InsertCommand =
cmd.GetInsertCommand(dt.Rows(j))
                         salesOF_DataAdapter.InsertCommand.Transaction = trans

                         salesOF_DataAdapter.Update(salesOFChanges)
                     End If
                 End If


                 trans.Commit()
                 m_SqlConnection.Close()
                 dirtySketch = False
                 BrowserPanel.Refresh()
             End If
         Catch ex As Exception
             MsgBox(" error on writing data " & ex.Message,
MsgBoxStyle.AbortRetryIgnore)
             If trans IsNot Nothing Then trans.Rollback()
             If m_SqlConnection.State = ConnectionState.Open Then
m_SqlConnection.Close()
         End Try
     End Sub

End Class